package com.gisuni.sddc.service;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.json.JSONArray;
import org.json.JSONObject;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import com.gisuni.sddc.dao.SpecialdataDao;
import com.gisuni.sddc.model.Response;
import com.gisuni.sddc.platformManager.dao.LayerTableNameDao;
import com.gisuni.sddc.util.ExcelOperation;
import com.gisuni.sddc.util.StringUtil;

@IocBean
public class SpecialdataService {

	private Log log = Logs.getLog(SpecialdataService.class);
	@Inject
	private SpecialdataDao specialdataDao;

	@Inject
	private LayerTableNameDao layerTableNameDao;



	/**
	 * 查询区域，行政区
	 * 通过qj表查询
	 * 
	 * @param
	 * @return
	 */
	public Response queryAreaOrDistinct(String type, String cityName) {

		Response response = new Response();
		response.setSuccess(false);
		String OrgResult = null;
		String LandResult = null;
		String selectstr = "";
		try {
			
	    	Map<String, String> map  = layerTableNameDao.getTableName();
	    	String tableName = map.get("QJ");
	    	
			if("1".equals(type)){//查询城市
				selectstr = "select distinct(csmc) resultData from "+tableName+" t";
			
			}else if("2".equals(type)){//查询行政区
				selectstr = "select fqmc resultData from "+tableName+" t where t.csmc ='"+cityName+"'";
			}

			OrgResult = specialdataDao.queryResultList(selectstr);

			if (OrgResult == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(OrgResult);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	/**
	 * 查询专题数据
	 * 
	 * @param
	 * @return
	 */
	public Response querySpecialdataList(String cityName, String distinctName) {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		StringBuffer selectstr = null;
		String condition = "where 1=1 ";
		if(!StringUtil.isNullOrEmpty(distinctName)){ //分区
			condition+=" and  t.fqmc='"+distinctName+"'";
		}else if(StringUtil.isNullOrEmpty(distinctName) && !StringUtil.isNullOrEmpty(cityName)){ //城市
			condition+=" and  t.csmc='"+cityName+"'";
		}
		
		Map<String, String> map  = layerTableNameDao.getTableName();

		
		
		
		try {
			/**
			 * select case   to_char(t3.jcfFqmc)  when '' then t3.lydFqmc else t3.lydFqmc end fqmc,t3.jcfCount,t3.jcfAreaSum,t3.lydCount,t3.lydAreaSum from (
				select * from  
				(select fqmc  jcfFqmc，count(1) jcfCount,sum(t.ydmj) jcfAreaSum from SDDCGISDATA.jcf t where t.csmc='广州市' group by fqmc ) t1,
				(select fqmc  lydFqmc，count(1) lydCount,sum(t.mj) lydAreaSum from SDDCGISDATA.lyd t where t.csmc='广州市' group by fqmc) t2 where t1.jcfFqmc(+) = t2.lydFqmc)  t3;

			 */
			 selectstr  = new StringBuffer();
//			 selectstr.append("");
//			 selectstr.append(" select case to_char(t3.jcfFqmc) when '' then t3.lydFqmc else t3.lydFqmc end fqmc, ");
//			 selectstr.append(" t3.jcfCount, t3.jcfAreaSum, t3.lydCount, t3.lydAreaSum from(select * from(select fqmc jcfFqmc,count(1) jcfCount,"); 
// 
//			 selectstr.append("	sum(t.ydmj) jcfAreaSum from SDDCGISDATA.jcf t  "+condition);
//			 selectstr.append("	group by fqmc) t1, (select fqmc lydFqmc， count(1) lydCount, sum(t.mj) lydAreaSum from SDDCGISDATA.lyd t "+condition);
// 
//		     selectstr.append("	group by fqmc) t2 where t1.jcfFqmc(+) = t2.lydFqmc) t3");
			 
			 
			 /**
			  *  bak
			 
			 selectstr.append(" select fqmc, case   when jcfCount>0    then jcfCount else 0 end jcfCount ,  case   when  jcfAreaSum>0 then jcfAreaSum else 0 end jcfAreaSum ,  ");   
					 selectstr.append("    case   when  lydCount>0 then lydCount else 0 end lydCount , case   when  lydAreaSum>0 then lydAreaSum else 0 end lydAreaSum   from (");
		     
		     if(!StringUtil.isNullOrEmpty(distinctName)){ // 选择区时列出镇的用地信息 
				 selectstr.append("    select to_char(t.zjmc) fqmc,t3.jcfCount, t3.jcfAreaSum ,t2.lydCount,t2.lydAreaSum from ( ");
				 selectstr.append(" select zjmc   from SDDCGISDATA.lyd t  "+condition+" group by t.zjmc ");
				 selectstr.append(" union ");
				 selectstr.append(" select zjmc   from SDDCGISDATA.jcf t  "+condition+" group by t.zjmc ) t");  //先查出旧厂房,留用地的镇街合集 再做关联
				 selectstr.append(" left join ");
				 selectstr.append("( select zjmc lydFqmc,   count(1) lydCount, sum(t.mj) lydAreaSum");
				 selectstr.append("  from SDDCGISDATA.lyd t ");
				 selectstr.append(condition+" group by zjmc) t2 on t.zjmc = t2.lydFqmc");
				 selectstr.append(" left join ");
				 selectstr.append("  (select zjmc jcfFqmc,   count(1) jcfCount, sum(t.ydmj) jcfAreaSum");
				 selectstr.append("  from SDDCGISDATA.jcf t ");
				 selectstr.append(condition+" group by zjmc) t3 on t.zjmc = t3.jcfFqmc") ;
				 selectstr.append(" union all ");
		     }
		     if(!StringUtil.isNullOrEmpty(distinctName)){
		    	 selectstr.append(" select  '合计' fqmc, ");
		     }else{
		    	 selectstr.append(" select  to_char(t3.fqmc) fqmc, ");
		     }
			 
			 selectstr.append(" t3.jcfCount, t3.jcfAreaSum, t3.lydCount, t3.lydAreaSum from(select * from(select fqmc from SDDCGISDATA.lyd t "+condition +" group by t.fqmc"); 
			 selectstr.append(" union select fqmc from SDDCGISDATA.jcf t "+condition +" group by t.fqmc) t"); //先查出旧厂房,留用地的分区合集
			 selectstr.append("	left join (select fqmc jcfFqmc,count(1) jcfCount,sum(t.ydmj) jcfAreaSum from SDDCGISDATA.jcf t  "+condition+"	group by fqmc) t1  on t.fqmc = t1.jcfFqmc ");
		     selectstr.append(" left join (select fqmc lydFqmc， count(1) lydCount, sum(t.mj) lydAreaSum from SDDCGISDATA.lyd t "+condition+" group by fqmc) t2 on t.fqmc = t2.lydFqmc) t3");
		     
		     selectstr.append(")");
			 Result = specialdataDao.queryResultList(selectstr.toString());
 */
			 
			 
			 selectstr.append(" select fqmc, case   when jcfCount>0    then jcfCount else 0 end jcfCount ,  case   when  jcfAreaSum>0 then jcfAreaSum else 0 end jcfAreaSum ,  ");   
					 selectstr.append("    case   when  lydCount>0 then lydCount else 0 end lydCount , case   when  lydAreaSum>0 then lydAreaSum else 0 end lydAreaSum   from (");
		     
		     if(!StringUtil.isNullOrEmpty(distinctName)){ // 选择区时列出镇的用地信息 
				 selectstr.append("    select to_char(t.zjmc) fqmc,t3.jcfCount, t3.jcfAreaSum ,t2.lydCount,t2.lydAreaSum from ( ");
				 selectstr.append(" select zjmc   from "+map.get("CZLD")+" t  "+condition+" group by t.zjmc ");
				 selectstr.append(" union ");
				 selectstr.append(" select zjmc   from "+map.get("SJJC")+" t  "+condition+" group by t.zjmc ) t");  //先查出旧厂房,留用地的镇街合集 再做关联
				 selectstr.append(" left join ");
				 selectstr.append("( select zjmc lydFqmc,   count(1) lydCount, sum(t.mj) lydAreaSum");
				 selectstr.append("  from "+map.get("CZLD")+" t ");
				 selectstr.append(condition+" group by zjmc) t2 on t.zjmc = t2.lydFqmc");
				 selectstr.append(" left join ");
				 selectstr.append("  (select zjmc jcfFqmc,   count(1) jcfCount, sum(t.ydmj) jcfAreaSum");
				 selectstr.append("  from "+map.get("SJJC")+" t ");
				 selectstr.append(condition+" group by zjmc) t3 on t.zjmc = t3.jcfFqmc") ;
				 selectstr.append(" union all ");
		     }
		     if(!StringUtil.isNullOrEmpty(distinctName)){
		    	 selectstr.append(" select  '合计' fqmc, ");
		     }else{
		    	 selectstr.append(" select  to_char(t3.fqmc) fqmc, ");
		     }
			 
			 selectstr.append(" t3.jcfCount, t3.jcfAreaSum, t3.lydCount, t3.lydAreaSum from(select * from(select fqmc from "+map.get("CZLD")+" t "+condition +" group by t.fqmc"); 
			 selectstr.append(" union select fqmc from "+map.get("SJJC")+" t "+condition +" group by t.fqmc) t"); //先查出旧厂房,留用地的分区合集
			 selectstr.append("	left join (select fqmc jcfFqmc,count(1) jcfCount,sum(t.ydmj) jcfAreaSum from "+map.get("SJJC")+" t  "+condition+"	group by fqmc) t1  on t.fqmc = t1.jcfFqmc ");
		     selectstr.append(" left join (select fqmc lydFqmc， count(1) lydCount, sum(t.mj) lydAreaSum from "+map.get("CZLD")+" t "+condition+" group by fqmc) t2 on t.fqmc = t2.lydFqmc) t3");
		     
		     selectstr.append(")");
			 Result = specialdataDao.queryResultList(selectstr.toString());
			 
			 
			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}
 

	/**
	 * 导出Excel
	 * @param departmentType
	 * @param projectType
	 * @param startdate
	 * @param enddate
	 * @param response
	 * @throws Exception
	 */
	public void exportlandSpecialdataExcel(String cityName, String distinctName,
			HttpServletResponse response) throws Exception {
		
		String filetitle = null;
		String[] rowsName = null;
		String[] columnTop = null;
		Response responseStr = querySpecialdataList(cityName, distinctName );
 
			filetitle = "专题数据统计报表";
			rowsName = new String[] { "序号", "行政区", "旧厂房","旧厂房", "留用地", "留用地"  };
			columnTop = new String[] { "", "FQMC", "JCFCOUNT","JCFAREASUM","LYDCOUNT",  "LYDAREASUM" };
	 
		String resultStr = (String) responseStr.getResult();
		JSONArray resultJsonArr = new JSONArray(resultStr);
		List<Map> Result = new ArrayList<Map>();
		if (resultJsonArr != null && resultJsonArr.length() > 0) {
			DecimalFormat    df   = new DecimalFormat("######0.00"); 
			for (int i = 0; i < resultJsonArr.length(); i++) {
				JSONObject ret = (JSONObject) resultJsonArr.get(i);
				Iterator iterator = ret.keys();
				Map map = new HashMap();
				while (iterator.hasNext()) {
					String key = (String) iterator.next();
					String value = (String) ret.get(key);
//					System.out.println("key:"+key +" value :"+value);
					if("JCFCOUNT".equals(key)||"JCFAREASUM".equals(key)||"LYDCOUNT".equals(key)||"LYDAREASUM".equals(key)){
						if(value==null || "".equals(value)){
							value="0";
						}else{
							value = df.format(Double.parseDouble(value));
						}
					}
					
					
					
					
					map.put(key, value);
				}
				Result.add(map);
			}
		}
		ExcelOperation excle = new ExcelOperation();
		excle.exportSpecil(filetitle, rowsName, columnTop, Result, response);
	}

 
}
